Typically we will just be either reading csv files directly or using pandas-datareader or quandl. Consider this lecture just a quick overview of what is possible with pandas (we won't be working with SQL or excel files in this course)

Data Input and Output

This notebook is the reference code for getting input and output, pandas can read a variety of file types using its pd.read_ methods. Let's take a look at the most common data types:



In [1]:

    
import numpy as np
import pandas as pd

CSV

CSV Input



In [2]:

    
df = pd.read_csv('example')
df

CSV Output



In [3]:

    
df.to_csv('example',
          index = False)

Excel

Pandas can read and write excel files, keep in mind, this only imports data. Not formulas or images, having images or macros may cause this read_excel method to crash.

Excel Input



In [4]:

    
pd.read_excel('Excel_Sample.xlsx',
              sheetname = 'Sheet1')

Excel Output



In [5]:

    
df.to_excel('Excel_Sample.xlsx',
            sheet_name = 'Sheet1')

HTML

You may need to install htmllib5,lxml, and BeautifulSoup4. In your terminal/command prompt run:

conda install lxml
conda install html5lib
conda install BeautifulSoup4

Then restart Jupyter Notebook. (or use pip install if you aren't using the Anaconda Distribution)

Pandas can read table tabs off of html. For example:

HTML Input

Pandas read_html function will read tables off of a webpage and return a list of DataFrame objects:



In [6]:

    
df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')



In [7]:

    
df[0]









    Out[7]:







  
    
      
      Bank Name
      City
      ST
      CERT
      Acquiring Institution
      Closing Date
      Updated Date
    
  
  
    
      0
      Fayette County Bank
      Saint Elmo
      IL
      1802
      United Fidelity Bank, fsb
      May 26, 2017
      July 26, 2017
    
    
      1
      Guaranty Bank, (d/b/a BestBank in Georgia & Mi...
      Milwaukee
      WI
      30003
      First-Citizens Bank & Trust Company
      May 5, 2017
      July 26, 2017
    
    
      2
      First NBC Bank
      New Orleans
      LA
      58302
      Whitney Bank
      April 28, 2017
      July 26, 2017
    
    
      3
      Proficio Bank
      Cottonwood Heights
      UT
      35495
      Cache Valley Bank
      March 3, 2017
      May 18, 2017
    
    
      4
      Seaway Bank and Trust Company
      Chicago
      IL
      19328
      State Bank of Texas
      January 27, 2017
      May 18, 2017
    
    
      5
      Harvest Community Bank
      Pennsville
      NJ
      34951
      First-Citizens Bank & Trust Company
      January 13, 2017
      May 18, 2017
    
    
      6
      Allied Bank
      Mulberry
      AR
      91
      Today's Bank
      September 23, 2016
      November 17, 2016
    
    
      7
      The Woodbury Banking Company
      Woodbury
      GA
      11297
      United Bank
      August 19, 2016
      June 1, 2017
    
    
      8
      First CornerStone Bank
      King of Prussia
      PA
      35312
      First-Citizens Bank & Trust Company
      May 6, 2016
      September 6, 2016
    
    
      9
      Trust Company Bank
      Memphis
      TN
      9956
      The Bank of Fayette County
      April 29, 2016
      September 6, 2016
    
    
      10
      North Milwaukee State Bank
      Milwaukee
      WI
      20364
      First-Citizens Bank & Trust Company
      March 11, 2016
      March 13, 2017
    
    
      11
      Hometown National Bank
      Longview
      WA
      35156
      Twin City Bank
      October 2, 2015
      April 13, 2016
    
    
      12
      The Bank of Georgia
      Peachtree City
      GA
      35259
      Fidelity Bank
      October 2, 2015
      October 24, 2016
    
    
      13
      Premier Bank
      Denver
      CO
      34112
      United Fidelity Bank, fsb
      July 10, 2015
      August 17, 2016
    
    
      14
      Edgebrook Bank
      Chicago
      IL
      57772
      Republic Bank of Chicago
      May 8, 2015
      July 12, 2016
    
    
      15
      Doral Bank  En Espanol
      San Juan
      PR
      32102
      Banco Popular de Puerto Rico
      February 27, 2015
      May 13, 2015
    
    
      16
      Capitol City Bank & Trust Company
      Atlanta
      GA
      33938
      First-Citizens Bank & Trust Company
      February 13, 2015
      April 21, 2015
    
    
      17
      Highland Community Bank
      Chicago
      IL
      20290
      United Fidelity Bank, fsb
      January 23, 2015
      April 21, 2015
    
    
      18
      First National Bank of Crestview
      Crestview
      FL
      17557
      First NBC Bank
      January 16, 2015
      May 8, 2017
    
    
      19
      Northern Star Bank
      Mankato
      MN
      34983
      BankVista
      December 19, 2014
      January 6, 2016
    
    
      20
      Frontier Bank, FSB D/B/A El Paseo Bank
      Palm Desert
      CA
      34738
      Bank of Southern California, N.A.
      November 7, 2014
      November 10, 2016
    
    
      21
      The National Republic Bank of Chicago
      Chicago
      IL
      916
      State Bank of Texas
      October 24, 2014
      January 6, 2016
    
    
      22
      NBRS Financial
      Rising Sun
      MD
      4862
      Howard Bank
      October 17, 2014
      March 26, 2015
    
    
      23
      GreenChoice Bank, fsb
      Chicago
      IL
      28462
      Providence Bank, LLC
      July 25, 2014
      December 12, 2016
    
    
      24
      Eastside Commercial Bank
      Conyers
      GA
      58125
      Community & Southern Bank
      July 18, 2014
      July 11, 2016
    
    
      25
      The Freedom State Bank
      Freedom
      OK
      12483
      Alva State Bank & Trust Company
      June 27, 2014
      March 25, 2016
    
    
      26
      Valley Bank
      Fort Lauderdale
      FL
      21793
      Landmark Bank, National Association
      June 20, 2014
      June 29, 2015
    
    
      27
      Valley Bank
      Moline
      IL
      10450
      Great Southern Bank
      June 20, 2014
      June 26, 2015
    
    
      28
      Slavie Federal Savings Bank
      Bel Air
      MD
      32368
      Bay Bank, FSB
      May 30, 2014
      December 12, 2016
    
    
      29
      Columbia Savings Bank
      Cincinnati
      OH
      32284
      United Fidelity Bank, fsb
      May 23, 2014
      November 10, 2016
    
    
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    
    
      523
      ANB Financial, NA
      Bentonville
      AR
      33901
      Pulaski Bank and Trust Company
      May 9, 2008
      August 28, 2012
    
    
      524
      Hume Bank
      Hume
      MO
      1971
      Security Bank
      March 7, 2008
      August 28, 2012
    
    
      525
      Douglass National Bank
      Kansas City
      MO
      24660
      Liberty Bank and Trust Company
      January 25, 2008
      October 26, 2012
    
    
      526
      Miami Valley Bank
      Lakeview
      OH
      16848
      The Citizens Banking Company
      October 4, 2007
      September 12, 2016
    
    
      527
      NetBank
      Alpharetta
      GA
      32575
      ING DIRECT
      September 28, 2007
      August 28, 2012
    
    
      528
      Metropolitan Savings Bank
      Pittsburgh
      PA
      35353
      Allegheny Valley Bank of Pittsburgh
      February 2, 2007
      October 27, 2010
    
    
      529
      Bank of Ephraim
      Ephraim
      UT
      1249
      Far West Bank
      June 25, 2004
      April 9, 2008
    
    
      530
      Reliance Bank
      White Plains
      NY
      26778
      Union State Bank
      March 19, 2004
      April 9, 2008
    
    
      531
      Guaranty National Bank of Tallahassee
      Tallahassee
      FL
      26838
      Hancock Bank of Florida
      March 12, 2004
      June 5, 2012
    
    
      532
      Dollar Savings Bank
      Newark
      NJ
      31330
      No Acquirer
      February 14, 2004
      April 9, 2008
    
    
      533
      Pulaski Savings Bank
      Philadelphia
      PA
      27203
      Earthstar Bank
      November 14, 2003
      July 22, 2005
    
    
      534
      First National Bank of Blanchardville
      Blanchardville
      WI
      11639
      The Park Bank
      May 9, 2003
      June 5, 2012
    
    
      535
      Southern Pacific Bank
      Torrance
      CA
      27094
      Beal Bank
      February 7, 2003
      October 20, 2008
    
    
      536
      Farmers Bank of Cheneyville
      Cheneyville
      LA
      16445
      Sabine State Bank & Trust
      December 17, 2002
      October 20, 2004
    
    
      537
      Bank of Alamo
      Alamo
      TN
      9961
      No Acquirer
      November 8, 2002
      March 18, 2005
    
    
      538
      AmTrade International Bank  En Espanol
      Atlanta
      GA
      33784
      No Acquirer
      September 30, 2002
      September 11, 2006
    
    
      539
      Universal Federal Savings Bank
      Chicago
      IL
      29355
      Chicago Community Bank
      June 27, 2002
      April 9, 2008
    
    
      540
      Connecticut Bank of Commerce
      Stamford
      CT
      19183
      Hudson United Bank
      June 26, 2002
      February 14, 2012
    
    
      541
      New Century Bank
      Shelby Township
      MI
      34979
      No Acquirer
      March 28, 2002
      March 18, 2005
    
    
      542
      Net 1st National Bank
      Boca Raton
      FL
      26652
      Bank Leumi USA
      March 1, 2002
      April 9, 2008
    
    
      543
      NextBank, NA
      Phoenix
      AZ
      22314
      No Acquirer
      February 7, 2002
      February 5, 2015
    
    
      544
      Oakwood Deposit Bank Co.
      Oakwood
      OH
      8966
      The State Bank & Trust Company
      February 1, 2002
      October 25, 2012
    
    
      545
      Bank of Sierra Blanca
      Sierra Blanca
      TX
      22002
      The Security State Bank of Pecos
      January 18, 2002
      November 6, 2003
    
    
      546
      Hamilton Bank, NA  En Espanol
      Miami
      FL
      24382
      Israel Discount Bank of New York
      January 11, 2002
      September 21, 2015
    
    
      547
      Sinclair National Bank
      Gravette
      AR
      34248
      Delta Trust & Bank
      September 7, 2001
      February 10, 2004
    
    
      548
      Superior Bank, FSB
      Hinsdale
      IL
      32646
      Superior Federal, FSB
      July 27, 2001
      August 19, 2014
    
    
      549
      Malta National Bank
      Malta
      OH
      6629
      North Valley Bank
      May 3, 2001
      November 18, 2002
    
    
      550
      First Alliance Bank & Trust Co.
      Manchester
      NH
      34264
      Southern New Hampshire Bank & Trust
      February 2, 2001
      February 18, 2003
    
    
      551
      National State Bank of Metropolis
      Metropolis
      IL
      3815
      Banterra Bank of Marion
      December 14, 2000
      March 17, 2005
    
    
      552
      Bank of Honolulu
      Honolulu
      HI
      21029
      Bank of the Orient
      October 13, 2000
      March 17, 2005
    
  

553 rows × 7 columns

	Bank Name	City	ST	CERT	Acquiring Institution	Closing Date	Updated Date
0	Fayette County Bank	Saint Elmo	IL	1802	United Fidelity Bank, fsb	May 26, 2017	July 26, 2017
1	Guaranty Bank, (d/b/a BestBank in Georgia & Mi...	Milwaukee	WI	30003	First-Citizens Bank & Trust Company	May 5, 2017	July 26, 2017
2	First NBC Bank	New Orleans	LA	58302	Whitney Bank	April 28, 2017	July 26, 2017
3	Proficio Bank	Cottonwood Heights	UT	35495	Cache Valley Bank	March 3, 2017	May 18, 2017
4	Seaway Bank and Trust Company	Chicago	IL	19328	State Bank of Texas	January 27, 2017	May 18, 2017
5	Harvest Community Bank	Pennsville	NJ	34951	First-Citizens Bank & Trust Company	January 13, 2017	May 18, 2017
6	Allied Bank	Mulberry	AR	91	Today's Bank	September 23, 2016	November 17, 2016
7	The Woodbury Banking Company	Woodbury	GA	11297	United Bank	August 19, 2016	June 1, 2017
8	First CornerStone Bank	King of Prussia	PA	35312	First-Citizens Bank & Trust Company	May 6, 2016	September 6, 2016
9	Trust Company Bank	Memphis	TN	9956	The Bank of Fayette County	April 29, 2016	September 6, 2016
10	North Milwaukee State Bank	Milwaukee	WI	20364	First-Citizens Bank & Trust Company	March 11, 2016	March 13, 2017
11	Hometown National Bank	Longview	WA	35156	Twin City Bank	October 2, 2015	April 13, 2016
12	The Bank of Georgia	Peachtree City	GA	35259	Fidelity Bank	October 2, 2015	October 24, 2016
13	Premier Bank	Denver	CO	34112	United Fidelity Bank, fsb	July 10, 2015	August 17, 2016
14	Edgebrook Bank	Chicago	IL	57772	Republic Bank of Chicago	May 8, 2015	July 12, 2016
15	Doral Bank En Espanol	San Juan	PR	32102	Banco Popular de Puerto Rico	February 27, 2015	May 13, 2015
16	Capitol City Bank & Trust Company	Atlanta	GA	33938	First-Citizens Bank & Trust Company	February 13, 2015	April 21, 2015
17	Highland Community Bank	Chicago	IL	20290	United Fidelity Bank, fsb	January 23, 2015	April 21, 2015
18	First National Bank of Crestview	Crestview	FL	17557	First NBC Bank	January 16, 2015	May 8, 2017
19	Northern Star Bank	Mankato	MN	34983	BankVista	December 19, 2014	January 6, 2016
20	Frontier Bank, FSB D/B/A El Paseo Bank	Palm Desert	CA	34738	Bank of Southern California, N.A.	November 7, 2014	November 10, 2016
21	The National Republic Bank of Chicago	Chicago	IL	916	State Bank of Texas	October 24, 2014	January 6, 2016
22	NBRS Financial	Rising Sun	MD	4862	Howard Bank	October 17, 2014	March 26, 2015
23	GreenChoice Bank, fsb	Chicago	IL	28462	Providence Bank, LLC	July 25, 2014	December 12, 2016
24	Eastside Commercial Bank	Conyers	GA	58125	Community & Southern Bank	July 18, 2014	July 11, 2016
25	The Freedom State Bank	Freedom	OK	12483	Alva State Bank & Trust Company	June 27, 2014	March 25, 2016
26	Valley Bank	Fort Lauderdale	FL	21793	Landmark Bank, National Association	June 20, 2014	June 29, 2015
27	Valley Bank	Moline	IL	10450	Great Southern Bank	June 20, 2014	June 26, 2015
28	Slavie Federal Savings Bank	Bel Air	MD	32368	Bay Bank, FSB	May 30, 2014	December 12, 2016
29	Columbia Savings Bank	Cincinnati	OH	32284	United Fidelity Bank, fsb	May 23, 2014	November 10, 2016
...	...	...	...	...	...	...	...
523	ANB Financial, NA	Bentonville	AR	33901	Pulaski Bank and Trust Company	May 9, 2008	August 28, 2012
524	Hume Bank	Hume	MO	1971	Security Bank	March 7, 2008	August 28, 2012
525	Douglass National Bank	Kansas City	MO	24660	Liberty Bank and Trust Company	January 25, 2008	October 26, 2012
526	Miami Valley Bank	Lakeview	OH	16848	The Citizens Banking Company	October 4, 2007	September 12, 2016
527	NetBank	Alpharetta	GA	32575	ING DIRECT	September 28, 2007	August 28, 2012
528	Metropolitan Savings Bank	Pittsburgh	PA	35353	Allegheny Valley Bank of Pittsburgh	February 2, 2007	October 27, 2010
529	Bank of Ephraim	Ephraim	UT	1249	Far West Bank	June 25, 2004	April 9, 2008
530	Reliance Bank	White Plains	NY	26778	Union State Bank	March 19, 2004	April 9, 2008
531	Guaranty National Bank of Tallahassee	Tallahassee	FL	26838	Hancock Bank of Florida	March 12, 2004	June 5, 2012
532	Dollar Savings Bank	Newark	NJ	31330	No Acquirer	February 14, 2004	April 9, 2008
533	Pulaski Savings Bank	Philadelphia	PA	27203	Earthstar Bank	November 14, 2003	July 22, 2005
534	First National Bank of Blanchardville	Blanchardville	WI	11639	The Park Bank	May 9, 2003	June 5, 2012
535	Southern Pacific Bank	Torrance	CA	27094	Beal Bank	February 7, 2003	October 20, 2008
536	Farmers Bank of Cheneyville	Cheneyville	LA	16445	Sabine State Bank & Trust	December 17, 2002	October 20, 2004
537	Bank of Alamo	Alamo	TN	9961	No Acquirer	November 8, 2002	March 18, 2005
538	AmTrade International Bank En Espanol	Atlanta	GA	33784	No Acquirer	September 30, 2002	September 11, 2006
539	Universal Federal Savings Bank	Chicago	IL	29355	Chicago Community Bank	June 27, 2002	April 9, 2008
540	Connecticut Bank of Commerce	Stamford	CT	19183	Hudson United Bank	June 26, 2002	February 14, 2012
541	New Century Bank	Shelby Township	MI	34979	No Acquirer	March 28, 2002	March 18, 2005
542	Net 1st National Bank	Boca Raton	FL	26652	Bank Leumi USA	March 1, 2002	April 9, 2008
543	NextBank, NA	Phoenix	AZ	22314	No Acquirer	February 7, 2002	February 5, 2015
544	Oakwood Deposit Bank Co.	Oakwood	OH	8966	The State Bank & Trust Company	February 1, 2002	October 25, 2012
545	Bank of Sierra Blanca	Sierra Blanca	TX	22002	The Security State Bank of Pecos	January 18, 2002	November 6, 2003
546	Hamilton Bank, NA En Espanol	Miami	FL	24382	Israel Discount Bank of New York	January 11, 2002	September 21, 2015
547	Sinclair National Bank	Gravette	AR	34248	Delta Trust & Bank	September 7, 2001	February 10, 2004
548	Superior Bank, FSB	Hinsdale	IL	32646	Superior Federal, FSB	July 27, 2001	August 19, 2014
549	Malta National Bank	Malta	OH	6629	North Valley Bank	May 3, 2001	November 18, 2002
550	First Alliance Bank & Trust Co.	Manchester	NH	34264	Southern New Hampshire Bank & Trust	February 2, 2001	February 18, 2003
551	National State Bank of Metropolis	Metropolis	IL	3815	Banterra Bank of Marion	December 14, 2000	March 17, 2005
552	Bank of Honolulu	Honolulu	HI	21029	Bank of the Orient	October 13, 2000	March 17, 2005